﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using C1.Win.C1FlexGrid;
using System.Transactions;
using Encrypt_a_string; 

namespace BHXH_NT
{
    public partial class frmKTHSCN : Form
    {
        DataTable tb;
        public frmKTHSCN()
        {
            InitializeComponent();
        }
        private void cmdImport_Click(object sender, EventArgs e)
        {

            OpenFileDialog oFile = new OpenFileDialog();
            oFile.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
            if (oFile.ShowDialog() == DialogResult.OK)
            {
                txtDuongdan.Text = oFile.FileName;
            }
            
        }
        private void FormatGrid()
        {
            fa.Cols[1].WidthDisplay = 30;
            fa.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[2].WidthDisplay = 120;
            fa.Cols["diachi"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["diachi"].WidthDisplay = 250;
        }
        private void FormatGrid1()
        {
            fa1.Cols[1].WidthDisplay = 30;
            //fa.Cols[1].WidthDisplay = 200;
            fa1.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[2].WidthDisplay = 120;
            fa1.Cols[8].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[8].WidthDisplay = 100;
            fa1.Cols[9].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[9].WidthDisplay = 50;
            fa1.Cols["gioitinh"].WidthDisplay = 40;
            fa1.Cols["gioitinh1"].WidthDisplay = 50;
            //fa1.Cols["diachi"].StyleDisplay.Font = new Font(".Vntime", 10);

        }
        private void GetdataToFrom()
        {
            dbConnection conn;
            conn = new dbConnection();
            string sql = "select * from KTHSCN order by stt";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa.DataSource = conn.executeSelectQuery(sql, sqlParameter);
        }
        private void Importfile()
        {
           

            string FileName = txtDuongdan.Text;
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FileName + " ;Extended Properties=Excel 8.0";

            bool flag = false;
            bool flag1 = false;
            bool flag2 = false;
            try
            {
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SqlConnection con = new SqlConnection(ModuleBHXH.sqlConnectionString))
                    {
                        con.Open();
                        // xoa ban
                        using (SqlCommand cmd = con.CreateCommand())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = "delete KTHSCN";
                            flag = cmd.ExecuteNonQuery() == 1;
                        }
                        con.Close();
                        using (SqlBulkCopy bulkCopy =
                          new SqlBulkCopy(ModuleBHXH.sqlConnectionString))
                          {
                                    bulkCopy.DestinationTableName = "KTHSCN";
                                    bulkCopy.WriteToServer(tb);
                                    flag1 = true;
                           }
                        
                        con.Open();
                        using (SqlCommand cmd = con.CreateCommand())
                        {
                            SqlDataAdapter myAdapter = new SqlDataAdapter();
                            DataSet ds = new DataSet();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText =  "select DISTINCT a.stt,a.hoten,a.sobhxh,a.socmnd,a.ngaysinh,a.gioitinh,b.madvi,b.ho,b.ten,"
                            + " b.sobhxh,b.ngaysinh,b.gioitinh,b.huyen from dbo.KTHSCN a inner join dbo.HOSOCN b "
                            + "on a.SOCMND = b.SOCMND ";
                            cmd.ExecuteNonQuery();
                            myAdapter.SelectCommand = cmd;
                            myAdapter.Fill(ds);
                            fa1.DataSource = ds.Tables[0];
                            flag2 = true;
                        }

                    }
                    

                    if (flag&&flag1 &&flag2 )
                    {
                        // To commit the transaction 
                        ts.Complete();
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }  
        }
        private void DeleteTable()
        {
            dbConnection conn;
            conn = new dbConnection();

            string sql = "delete KTHSCN";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa.DataSource = conn.executeInsertQuery(sql, sqlParameter);
        }
        public DataSet importDataFromExcel()
        {
            //string FileName = System.IO.Directory.GetCurrentDirectory().ToString() + "\\thu_bhxh.xls";
            string FileName = txtDuongdan.Text;
            OleDbDataAdapter myAdapter = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            // Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FileName + " ;Extended Properties=Excel 8.0";

            // Create Connection to Excel Workbook
            using (OleDbConnection connection =
                         new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                        ("Select * FROM [Sheet1$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(ds);
                // SQL Server Connection String
                //string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;
                //// Bulk Copy to SQL Server
                //using (SqlBulkCopy bulkCopy =
                //           new SqlBulkCopy(sqlConnectionString))
                //{
                //    bulkCopy.DestinationTableName = "KTHSCN";
                //    bulkCopy.WriteToServer(ds.Tables[0]);

                //}

            }
            return ds;
        }
        private void kiemtracmt()
        {
            dbConnection conn;
            conn = new dbConnection();
            Cursor.Current = Cursors.WaitCursor;
            string sql = "select DISTINCT a.stt,a.hoten,a.sobhxh,a.SOCMND,a.ngaysinh,a.gioitinh,b.madvi,b.HO,b.TEN,"
            + " b.sobhxh,b.ngaysinh,b.gioitinh,b.huyen from dbo.KTHSCN a inner join dbo.HOSOCN b "
            + "on a.SOCMND = b.SOCMND ";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa1.DataSource = conn.executeSelectQuery(sql, sqlParameter);
            FormatGrid1();
        }
        private void cmdKTCMND_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Importfile();
            FormatGrid1();
            Cursor.Current = Cursors.Default;
            //MessageBox.Show("Import d÷ liÖu thµnh c«ng");

            
        }

        private void frmKTHSCN_Load(object sender, EventArgs e)
        {
            txtDuongdan.Text =  System.IO.Directory.GetCurrentDirectory().ToString() + "\\thu_bhxh.xls";
        }

        private void btnSolieu_Click(object sender, EventArgs e)
        {
            tb = importDataFromExcel().Tables[0];
            fa.DataSource = tb;
            FormatGrid();
        }
        private void btnKtcmt_Click(object sender, EventArgs e)
        {
            dbConnection conn;
            conn = new dbConnection();
            Cursor.Current = Cursors.WaitCursor;
            string sql = "select DISTINCT b.madvi,b.ho,b.ten,"
            + " b.sobhxh,b.ngaysinh,b.gioitinh,b.diachi,b.huyen from dbo.HOSOCN b "
            + " where b.SOCMND = '" + txtCMND.Text +"'";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa1.DataSource = conn.executeSelectQuery(sql, sqlParameter);
            FormatGrid2();
        }
        private void FormatGrid2()
        {
            fa1.Cols[1].WidthDisplay = 60;
            fa1.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[2].WidthDisplay = 100;
            fa1.Cols[3].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[3].WidthDisplay = 50;
            fa1.Cols["gioitinh"].WidthDisplay = 40;
            fa1.Cols["diachi"].WidthDisplay = 300;
            fa1.Cols["diachi"].StyleDisplay.Font = new Font(".Vntime", 10);
   
        }
       
    }
}
